package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import data.dao.*;
import data.dto.*;
import data.searchdto.*;

public class UserDAOImpl implements UserDAO {

	@Override
	public UserDTO findUser(UserSearchDTO usersearchdto)
			throws UserDAOException {

		String sql = "SELECT * FROM user WHERE isactive = 1 AND id = ? ";
		PreparedStatement stmt = null;
		UserDTO user = null;
		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, usersearchdto.getId());

			user = singleQuery(usersearchdto);

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return user;
	}
	
	@Override
	public UserDTO findUser(String username, String password)
			throws UserDAOException {

		String sql = "SELECT * FROM user WHERE isactive = 1 AND username = ? AND password = ? ";
		PreparedStatement stmt = null;
		UserDTO valueObject = null;
		ResultSet result = null;
		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setString(1, username);
			stmt.setString(2, password);

			result = util.Database.getInstance().executeQuery(stmt);

			if (result.next()) {

				valueObject = new UserDTO();
				valueObject.setId(result.getInt("id"));
				valueObject.setUserType(result.getShort("userType"));
				valueObject.setUserName(result.getString("userName"));
				valueObject.setPassword(result.getString("password"));
				valueObject.setName(result.getString("name"));
				valueObject.setAddress(result.getString("address"));
				valueObject.setPhone(result.getString("phone"));
				valueObject.setOverTime(result.getDouble("overtime"));
				valueObject.setStatus(result.getShort("status"));
				valueObject.setUserrank(result.getShort("userRank"));
			}
			

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return valueObject;
	}

	@Override
	public ArrayList<UserDTO> findAllUser() throws UserDAOException {
		String sql = "SELECT * FROM user WHERE isactive = 1 ORDER BY id ASC ";
		ArrayList<UserDTO> searchResults=null;
		try {
			searchResults = listQuery(util.Database.getInstance().getConnection().prepareStatement(sql));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return searchResults;
	}

	@Override
	public void insertUserDetail(UserDTO valueObject) throws UserDAOException {

		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO user (userType, userName, "
					+ "password, name, address, "
					+ "phone, overtime, "
					+ "status, userRank,isactive) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,1) ";
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			//stmt.setInt(1, valueObject.getId());
			stmt.setInt(1, valueObject.getUserType());
			stmt.setString(2, valueObject.getUserName());
			stmt.setString(3, valueObject.getPassword());
			stmt.setString(4, valueObject.getName());
			stmt.setString(5, valueObject.getAddress());
			stmt.setString(6, valueObject.getPhone());
			stmt.setDouble(7, valueObject.getOverTime());
			//stmt.setInt(8, valueObject.getCreatedBy());
			//stmt.setTimestamp(9, util.CurrentDate.getCurrentTimeStamp());
			//stmt.setInt(10, valueObject.getModifiedBy());
			//stmt.setTimestamp(11, util.CurrentDate.getCurrentTimeStamp());
			stmt.setShort(8, valueObject.getStatus());
			stmt.setInt(9, valueObject.getUserrank());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	@Override
	public void updateUserDetail(UserDTO valueObject) throws UserDAOException {

		String sql = "UPDATE user SET userType = ?, userName = ?, password = ?, "
				+ "name = ?, address = ?, phone = ?, "
				+ "overtime = ?, "
				+ "status = ?, "
				+ "userRank = ? WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, valueObject.getUserType());
			stmt.setString(2, valueObject.getUserName());
			stmt.setString(3, valueObject.getPassword());
			stmt.setString(4, valueObject.getName());
			stmt.setString(5, valueObject.getAddress());
			stmt.setString(6, valueObject.getPhone());
			stmt.setDouble(7, valueObject.getOverTime());
			//stmt.setInt(8, valueObject.getModifiedBy());
			//stmt.setTimestamp(9, util.CurrentDate.getCurrentTimeStamp());
			stmt.setShort(8, valueObject.getStatus());
			stmt.setInt(9, valueObject.getUserrank());

			stmt.setInt(10, valueObject.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	@Override
	public void deleteUserDetail(UserDTO User) throws NotFoundException,
			SQLException {

		String sql = "UPDATE user SET isactive = 0 WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, User.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	public String searchMatching(UserSearchDTO valueObject) throws SQLException {

		boolean first = true;
		StringBuffer sql = new StringBuffer("SELECT * FROM user WHERE isactive = 1 ");

		if (valueObject.getId() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND id = ").append(valueObject.getId()).append(" ");
		}

		if (valueObject.getName() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND name LIKE '").append(valueObject.getName())
					.append("%' ");
		}

		if (valueObject.getUserrank() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND Userrank LIKE '").append(valueObject.getUserrank())
					.append("%' ");
		}

		if (valueObject.getUsertype() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND Usertype LIKE '").append(valueObject.getUsertype())
					.append("%' ");
		}
		
		if (valueObject.getManagetid() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND managerid LIKE '").append(valueObject.getManagetid())
					.append("%' ");
		}

		sql.append("ORDER BY id ASC ");

		return sql.toString();
	}

	protected UserDTO singleQuery(
			UserSearchDTO userSearchDTO) throws NotFoundException, SQLException {

		UserDTO valueObject = null;
		ResultSet result = null;
		PreparedStatement stmt = null;

		try {
			String sql = searchMatching(userSearchDTO);
			stmt = util.Database.getInstance().getConnection().prepareStatement(sql);
			result = util.Database.getInstance().executeQuery(stmt);

			if (result.next()) {

				valueObject = new UserDTO();
				valueObject.setId(result.getInt("id"));
				valueObject.setUserType(result.getShort("usertype"));
				valueObject.setUserName(result.getString("username"));
				valueObject.setPassword(result.getString("password"));
				valueObject.setName(result.getString("name"));
				valueObject.setAddress(result.getString("address"));
				valueObject.setPhone(result.getString("phone"));
				valueObject.setOverTime(result.getDouble("overtime"));
				//valueObject.setCreatedBy(result.getInt("createdBy"));
				//valueObject.setCreatedOn(result.getTimestamp("createdOn"));
				//valueObject.setModifiedBy(result.getInt("modifiedBy"));
				//valueObject.setModifiedOn(result.getTimestamp("modifiedOn"));
				valueObject.setStatus(result.getShort("status"));
				valueObject.setUserrank(result.getShort("userrank"));

			} else {
				// System.out.println("User Object Not Found!");
				throw new NotFoundException("User Object Not Found!");
			}
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (result != null)
				try {
					result.close();

					if (stmt != null)
						stmt.close();
				} catch (java.sql.SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return valueObject;
	}

	protected ArrayList<UserDTO> listQuery(PreparedStatement stmt)
			throws SQLException {

		ArrayList<UserDTO> searchResults = new ArrayList<UserDTO>();

		ResultSet result = null;
		try {
			result = stmt.executeQuery();

			while (result.next()) {
				UserDTO temp = new UserDTO();

				temp.setId(result.getInt("id"));
				temp.setUserType(result.getShort("userType"));
				temp.setUserName(result.getString("userName"));
				temp.setPassword(result.getString("password"));
				temp.setName(result.getString("name"));
				temp.setAddress(result.getString("address"));
				temp.setPhone(result.getString("phone"));
				temp.setOverTime(result.getDouble("overtime"));
				//temp.setCreatedBy(result.getInt("createdBy"));
				//temp.setCreatedOn(result.getTimestamp("createdOn"));
				//temp.setModifiedBy(result.getInt("modifiedBy"));
				//temp.setModifiedOn(result.getTimestamp("modifiedOn"));
				temp.setStatus(result.getShort("status"));
				temp.setUserrank(result.getShort("userRank"));

				searchResults.add(temp);
			}

		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (result != null)
					result.close();

				if (stmt != null)
					stmt.close();
			} catch (java.sql.SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return searchResults;
	}

}
